MS Access is able for doing transaction-based processing and passes them to any of the backend DBMS system. As the result, transaction is managed by the DBMS rather than by MS Access. SQL commands are passed to DBMS and executed by using ADO Connection object. Here is an example shown to explain the complete criteria.

We have created a table with the fields, Structure of the table is shown in Fig: - 1.1.

Create a form with a button for which you will implement the Code. As shown in Fig: - 1.2.

When you will click on to the button then connection will get established and transaction will start and after successful transaction a message box will appear, as shown in Fig: - 1.3.

After the successful transaction, a new record will get stored into the table, shown in Fig: - 1.4.

If the data already exist into the table and you will click on to the transaction button then a message box will open telling about the failure of transaction. As shown below in Fig: - 1.5

Here is the complete VBA code given for the above procedure :-

Note:-Please do add the reference of Microsoft ActiveX Data Objects 2.x Library.

---------------Code for On Click event of the "Transaction" button---------------

Private Sub cmdTransaction_Click()
If AddDataToAccess Then
MSgBox "Transaction successfully made", vbInformation, "Success"
Else
MSgBox "Transaction Failed", vbInformation, "Failure"
End If
End Sub